﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Globalization;
using System.Linq;
using System.Reflection;
using KamLib.Converts;

namespace KamLib
{
    public static class SystemDataHelper
    {

        #region DbTypes

        public static readonly SqlDbType[] SqlDbBoolTypes = new[] { SqlDbType.Bit };
        public static readonly SqlDbType[] SqlDbDateTimeTypes = new[] { SqlDbType.Date, SqlDbType.DateTime, SqlDbType.DateTime2, SqlDbType.DateTimeOffset, SqlDbType.Time, SqlDbType.Timestamp, SqlDbType.Udt };
        public static readonly SqlDbType[] SqlDbFloatTypes = new[] { SqlDbType.Decimal, SqlDbType.Float, SqlDbType.Money, SqlDbType.Real, SqlDbType.SmallMoney };
        public static readonly SqlDbType[] SqlDbIntTypes = new[] { SqlDbType.BigInt, SqlDbType.Int, SqlDbType.SmallInt, SqlDbType.TinyInt };
        public static readonly SqlDbType[] SqlDbStringTypes = new[] { SqlDbType.Char, SqlDbType.NChar, SqlDbType.NText, SqlDbType.NVarChar, SqlDbType.Text, SqlDbType.VarChar, SqlDbType.Xml };
        //public static readonly SqlDbType[] SqlDbTypes = new[] {  };

        public static bool IsBool(this SqlDbType value)
        {
            return Array.IndexOf(SqlDbBoolTypes, value) >= 0;
        }
        public static bool IsDateTime(this SqlDbType value)
        {
            return Array.IndexOf(SqlDbDateTimeTypes, value) >= 0;
        }
        public static bool IsFloat(this SqlDbType value)
        {
            return Array.IndexOf(SqlDbFloatTypes, value) >= 0;
        }
        public static bool IsInt(this SqlDbType value)
        {
            return Array.IndexOf(SqlDbIntTypes, value) >= 0;
        }
        public static bool IsString(this SqlDbType value)
        {
            return Array.IndexOf(SqlDbStringTypes, value) >= 0;
        }

        #endregion


        #region Core

        public enum DbCoreType { MSSQL, MySQL, MSAccess }
        public static DbCoreType CurrentCoreType = DbCoreType.MSSQL;

        public static Func<object, object> NormalizeValue;
        public static Func<Type, Type> NormalizeType = a => a;

        //public static object PrepareValue(object value)
        //{
        //    if (CurrentCoreType == DbCoreType.MySQL)
        //    {
        //        if (value != null && value.GetType().Name == "MySqlDateTime")
        //            value = (DateTime)value;
        //    }
        //    return value;
        //}

        #endregion


        #region Params

        public static string ToParam(object value)
        {
            if (value == null || Convert.IsDBNull(value))
                return "null";
            if (value is string)
                return ToStringParam((string)value);
            if (value is DateTime)
                return ToDateTimeParam((DateTime)value);
            //if (value is DateTime?)
            //    return ToDateTimeParam((DateTime?)value);
            if (value is double)
                return ToFloatParam((double)value);
            //if (value is double?)
            //    return ToFloatParam((double?)value);
            if (value is bool)
                return ToBoolParam((bool)value);
            //if (value is bool?)
            //    return ToBoolParam((bool?)value);
            return value.ToString();
        }
        public static string ToParam(this IDbConnection connection, object value)
        {
            return ToParam(value);
        }



        public static Func<bool, string> ToBoolParamHandler;
        public static string ToBoolParam(bool value)
        {
            return ToBoolParamHandler != null
                ? ToBoolParamHandler(value)
                : CurrentCoreType == DbCoreType.MSAccess
                    ? value ? "true" : "false"
                    : value ? "1" : "0";
        }
        public static string ToBoolParam(bool? value)
        {
            return value.HasValue ? ToBoolParam(value.Value) : "null";
        }

        public static string ToBoolParam(this IDbConnection connection, bool value)
        {
            return ToBoolParam(value);
        }
        public static string ToBoolParam(this IDbConnection connection, bool? value)
        {
            return ToBoolParam(value);
        }


        public static string ToDateTimeParam(DateTime value)
        {
            return "'" + value.ToString("yyyy-MM-dd HH:mm:ss") + "'";
        }
        public static string ToDateTimeParam(DateTime? value)
        {
            return value.HasValue ? ToDateTimeParam(value.Value) : "null";
        }

        public static string ToDateTimeParam(this IDbConnection connection, DateTime value)
        {
            return ToDateTimeParam(value);
        }
        public static string ToDateTimeParam(this IDbConnection connection, DateTime? value)
        {
            return ToDateTimeParam(value);
        }


        public static string ToFloatParam(double value)
        {
            return value.ToString(CultureInfo.InvariantCulture);
        }
        public static string ToFloatParam(double? value)
        {
            return value.HasValue ? ToFloatParam(value.Value) : "null";
        }

        public static string ToFloatParam(this IDbConnection connection, double value)
        {
            return ToFloatParam(value);
        }
        public static string ToFloatParam(this IDbConnection connection, double? value)
        {
            return ToFloatParam(value);
        }


        public static string ToIntParam(int value)
        {
            return value.ToString();
        }
        public static string ToIntParam(int? value)
        {
            return value.HasValue ? ToIntParam(value.Value) : "null";
        }

        public static string ToIntParam(this IDbConnection connection, int value)
        {
            return ToIntParam(value);
        }
        public static string ToIntParam(this IDbConnection connection, int? value)
        {
            return ToIntParam(value);
        }


        public static string ToStringParam(string value)
        {
            return value == null ? "null" : "'" + value.Replace("'", "''") + "'";
        }

        public static string ToStringParam(this IDbConnection connection, string value)
        {
            return ToStringParam(value);
        }


        public static string[] ToParams(params object[] values)
        {
            var sparam = new string[values.Length];
            for (int i = 0, len = values.Length; i < len; i++)
                sparam[i] = ToParam(values[i]);
            return sparam;
        }

        public static string[] ToParams(this IDbConnection connection, params object[] values)
        {
            return ToParams(values);
        }


        public static string ToQuery(string commandText, params object[] param)
        {
            return String.Format(commandText, ToParams(param));
        }

        public static string ToQuery(this IDbConnection connection, string commandText, params object[] param)
        {
            return ToQuery(commandText, param);
        }

        #endregion


        #region Command

        [DebuggerStepThrough]
        public static IDbCommand NewCommand(this IDbConnection connection, string commandText)
        {
            var cmd = connection.CreateCommand();
            cmd.CommandText = commandText;
            return cmd;
        }
        [DebuggerStepThrough]
        public static IDbCommand NewCommand(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            var cmd = connection.CreateCommand();
            cmd.Transaction = tran;
            cmd.CommandText = commandText;
            return cmd;
        }
        [DebuggerStepThrough]
        public static IDbCommand NewCommand(this IDbConnection connection, string commandText, params object[] param)
        {
            return NewCommand(connection, connection.ToQuery(commandText, param));
        }
        [DebuggerStepThrough]
        public static IDbCommand NewCommand(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            return NewCommand(connection, tran, connection.ToQuery(commandText, param));
        }

        #endregion


        #region Connection

        public static IDbConnection OpenSafe(this IDbConnection connection)
        {
            if (connection != null && connection.State == ConnectionState.Closed)
                connection.Open();
            return connection;
        }

        #endregion


        #region Exec

        [DebuggerStepThrough]
        public static int Exec(this IDbConnection connection, string commandText)
        {
            connection.OpenSafe();
            return NewCommand(connection, commandText).ExecuteNonQuery();
        }
        [DebuggerStepThrough]
        public static int Exec(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            connection.OpenSafe();
            return NewCommand(connection, tran, commandText).ExecuteNonQuery();
        }
        [DebuggerStepThrough]
        public static int Exec(this IDbConnection connection, string commandText, params object[] param)
        {
            connection.OpenSafe();
            return NewCommand(connection, commandText, param).ExecuteNonQuery();
        }
        [DebuggerStepThrough]
        public static int Exec(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            connection.OpenSafe();
            return NewCommand(connection, tran, commandText, param).ExecuteNonQuery();
        }

        [DebuggerStepThrough]
        public static int ExecInTran(this IDbConnection connection, string commandText)
        {
            connection.OpenSafe();
            using (var tran = connection.BeginTransaction())
            {
                try
                {
                    var result = connection.Exec(tran, commandText);
                    tran.Commit();
                    return result;
                }
                catch
                {
                    tran.Rollback();
                    throw;
                }
            }
        }
        [DebuggerStepThrough]
        public static int ExecInTran(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecInTran(connection, ToQuery(connection, commandText, param));
        }

        #endregion


        #region ExecScalar

        [DebuggerStepThrough]
        public static T ExecScalar<T>(this IDbCommand cmd)
        {
            cmd.Connection.OpenSafe();
            return cmd.ExecuteScalar().To<T>();
        }

        [DebuggerStepThrough]
        public static T ExecScalar<T>(this IDbConnection connection, string commandText)
        {
            return NewCommand(connection, commandText).ExecScalar<T>();
        }
        [DebuggerStepThrough]
        public static T ExecScalar<T>(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            connection.OpenSafe();
            return NewCommand(connection, tran, commandText).ExecScalar<T>();
        }
        [DebuggerStepThrough]
        public static T ExecScalar<T>(this IDbConnection connection, string commandText, params object[] param)
        {
            connection.OpenSafe();
            return NewCommand(connection, commandText, param).ExecScalar<T>();
        }
        [DebuggerStepThrough]
        public static T ExecScalar<T>(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            connection.OpenSafe();
            return NewCommand(connection, tran, commandText, param).ExecScalar<T>();
        }

        [DebuggerStepThrough]
        public static T ExecScalarInTran<T>(this IDbConnection connection, string commandText)
        {
            connection.OpenSafe();
            using (var tran = connection.BeginTransaction())
            {
                try
                {
                    var result = connection.ExecScalar<T>(tran, commandText);
                    tran.Commit();
                    return result;
                }
                catch
                {
                    tran.Rollback();
                    throw;
                }
            }
        }
        [DebuggerStepThrough]
        public static object ExecScalarInTran<T>(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecScalarInTran<T>(connection, ToQuery(connection, commandText, param));
        }


        [DebuggerStepThrough]
        public static object ExecScalar(this IDbConnection connection, string commandText)
        {
            connection.OpenSafe();
            return NewCommand(connection, commandText).ExecuteScalar();
        }
        [DebuggerStepThrough]
        public static object ExecScalar(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            connection.OpenSafe();
            return NewCommand(connection, tran, commandText).ExecuteScalar();
        }
        [DebuggerStepThrough]
        public static object ExecScalar(this IDbConnection connection, string commandText, params object[] param)
        {
            connection.OpenSafe();
            return NewCommand(connection, commandText, param).ExecuteScalar();
        }
        [DebuggerStepThrough]
        public static object ExecScalar(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            connection.OpenSafe();
            return NewCommand(connection, tran, commandText, param).ExecuteScalar();
        }

        [DebuggerStepThrough]
        public static object ExecScalarInTran(this IDbConnection connection, string commandText)
        {
            connection.OpenSafe();
            using (var tran = connection.BeginTransaction())
            {
                try
                {
                    var result = connection.ExecScalar(tran, commandText);
                    tran.Commit();
                    return result;
                }
                catch
                {
                    tran.Rollback();
                    throw;
                }
            }
        }
        [DebuggerStepThrough]
        public static object ExecScalarInTran(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecScalarInTran(connection, ToQuery(connection, commandText, param));
        }

        #endregion



        #region ExecBool

        [DebuggerStepThrough]
        public static bool ExecBool(this IDbConnection connection, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), commandText).ToBool();
        }
        [DebuggerStepThrough]
        public static bool ExecBool(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText).ToBool();
        }
        [DebuggerStepThrough]
        public static bool ExecBool(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), commandText, param).ToBool();
        }
        [DebuggerStepThrough]
        public static bool ExecBool(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText, param).ToBool();
        }

        [DebuggerStepThrough]
        public static bool ExecBoolInTran(this IDbConnection connection, string commandText)
        {
            return ExecScalarInTran(connection.OpenSafe(), commandText).ToBool();
        }
        [DebuggerStepThrough]
        public static bool ExecBoolInTran(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecBoolInTran(connection, ToQuery(connection, commandText, param));
        }


        [DebuggerStepThrough]
        public static bool? ExecBooln(this IDbConnection connection, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), commandText).ToBooln();
        }
        [DebuggerStepThrough]
        public static bool? ExecBooln(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText).ToBooln();
        }
        [DebuggerStepThrough]
        public static bool? ExecBooln(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), commandText, param).ToBooln();
        }
        [DebuggerStepThrough]
        public static bool? ExecBooln(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText, param).ToBooln();
        }

        [DebuggerStepThrough]
        public static bool? ExecBoolnInTran(this IDbConnection connection, string commandText)
        {
            return ExecScalarInTran(connection.OpenSafe(), commandText).ToBooln();
        }
        [DebuggerStepThrough]
        public static bool? ExecBoolnInTran(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecBoolnInTran(connection, ToQuery(connection, commandText, param));
        }

        #endregion


        #region ExecDate

        [DebuggerStepThrough]
        public static DateTime ExecDate(this IDbConnection connection, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), commandText).ToDateTime();
        }
        [DebuggerStepThrough]
        public static DateTime ExecDate(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText).ToDateTime();
        }
        [DebuggerStepThrough]
        public static DateTime ExecDate(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), commandText, param).ToDateTime();
        }
        [DebuggerStepThrough]
        public static DateTime ExecDate(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText, param).ToDateTime();
        }

        [DebuggerStepThrough]
        public static DateTime ExecDateInTran(this IDbConnection connection, string commandText)
        {
            return ExecScalarInTran(connection.OpenSafe(), commandText).ToDateTime();
        }
        [DebuggerStepThrough]
        public static DateTime ExecDateInTran(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecDateInTran(connection, ToQuery(connection, commandText, param));
        }


        [DebuggerStepThrough]
        public static DateTime? ExecDaten(this IDbConnection connection, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), commandText).ToDateTimen();
        }
        [DebuggerStepThrough]
        public static DateTime? ExecDaten(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText).ToDateTimen();
        }
        [DebuggerStepThrough]
        public static DateTime? ExecDaten(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), commandText, param).ToDateTimen();
        }
        [DebuggerStepThrough]
        public static DateTime? ExecDaten(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText, param).ToDateTimen();
        }

        [DebuggerStepThrough]
        public static DateTime? ExecDatenInTran(this IDbConnection connection, string commandText)
        {
            return ExecScalarInTran(connection.OpenSafe(), commandText).ToDateTimen();
        }
        [DebuggerStepThrough]
        public static DateTime? ExecDatenInTran(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecDatenInTran(connection, ToQuery(connection, commandText, param));
        }

        #endregion


        #region ExecDouble

        [DebuggerStepThrough]
        public static double ExecDouble(this IDbConnection connection, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), commandText).ToDouble();
        }
        [DebuggerStepThrough]
        public static double ExecDouble(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText).ToDouble();
        }
        [DebuggerStepThrough]
        public static double ExecDouble(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), commandText, param).ToDouble();
        }
        [DebuggerStepThrough]
        public static double ExecDouble(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText, param).ToDouble();
        }

        [DebuggerStepThrough]
        public static double ExecDoubleInTran(this IDbConnection connection, string commandText)
        {
            return ExecScalarInTran(connection.OpenSafe(), commandText).ToDouble();
        }
        [DebuggerStepThrough]
        public static double ExecDoubleInTran(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecDoubleInTran(connection, ToQuery(connection, commandText, param));
        }


        [DebuggerStepThrough]
        public static double? ExecDoublen(this IDbConnection connection, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), commandText).ToDoublen();
        }
        [DebuggerStepThrough]
        public static double? ExecDoublen(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText).ToDoublen();
        }
        [DebuggerStepThrough]
        public static double? ExecDoublen(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), commandText, param).ToDoublen();
        }
        [DebuggerStepThrough]
        public static double? ExecDoublen(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText, param).ToDoublen();
        }

        [DebuggerStepThrough]
        public static double? ExecDoublenInTran(this IDbConnection connection, string commandText)
        {
            return ExecScalarInTran(connection.OpenSafe(), commandText).ToDoublen();
        }
        [DebuggerStepThrough]
        public static double? ExecDoublenInTran(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecDoublenInTran(connection, ToQuery(connection, commandText, param));
        }

        #endregion


        #region ExecDecimal

        [DebuggerStepThrough]
        public static decimal ExecDecimal(this IDbConnection connection, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), commandText).ToDecimal();
        }
        [DebuggerStepThrough]
        public static decimal ExecDecimal(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText).ToDecimal();
        }
        [DebuggerStepThrough]
        public static decimal ExecDecimal(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), commandText, param).ToDecimal();
        }
        [DebuggerStepThrough]
        public static decimal ExecDecimal(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText, param).ToDecimal();
        }

        [DebuggerStepThrough]
        public static decimal ExecDecimalInTran(this IDbConnection connection, string commandText)
        {
            return ExecScalarInTran(connection.OpenSafe(), commandText).ToDecimal();
        }
        [DebuggerStepThrough]
        public static decimal ExecDecimalInTran(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecDecimalInTran(connection, ToQuery(connection, commandText, param));
        }


        [DebuggerStepThrough]
        public static decimal? ExecDecimaln(this IDbConnection connection, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), commandText).ToDecimaln();
        }
        [DebuggerStepThrough]
        public static decimal? ExecDecimaln(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText).ToDecimaln();
        }
        [DebuggerStepThrough]
        public static decimal? ExecDecimaln(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), commandText, param).ToDecimaln();
        }
        [DebuggerStepThrough]
        public static decimal? ExecDecimaln(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText, param).ToDecimaln();
        }

        [DebuggerStepThrough]
        public static decimal? ExecDecimalnInTran(this IDbConnection connection, string commandText)
        {
            return ExecScalarInTran(connection.OpenSafe(), commandText).ToDecimaln();
        }
        [DebuggerStepThrough]
        public static decimal? ExecDecimalnInTran(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecDecimalnInTran(connection, ToQuery(connection, commandText, param));
        }

        #endregion


        #region ExecFloat

        [DebuggerStepThrough]
        public static float ExecFloat(this IDbConnection connection, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), commandText).ToFloat();
        }
        [DebuggerStepThrough]
        public static float ExecFloat(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText).ToFloat();
        }
        [DebuggerStepThrough]
        public static float ExecFloat(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), commandText, param).ToFloat();
        }
        [DebuggerStepThrough]
        public static float ExecFloat(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText, param).ToFloat();
        }

        [DebuggerStepThrough]
        public static float ExecFloatInTran(this IDbConnection connection, string commandText)
        {
            return ExecScalarInTran(connection.OpenSafe(), commandText).ToFloat();
        }
        [DebuggerStepThrough]
        public static float ExecFloatInTran(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecFloatInTran(connection, ToQuery(connection, commandText, param));
        }


        [DebuggerStepThrough]
        public static float? ExecFloatn(this IDbConnection connection, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), commandText).ToFloatn();
        }
        [DebuggerStepThrough]
        public static float? ExecFloatn(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText).ToFloatn();
        }
        [DebuggerStepThrough]
        public static float? ExecFloatn(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), commandText, param).ToFloatn();
        }
        [DebuggerStepThrough]
        public static float? ExecFloatn(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText, param).ToFloatn();
        }

        [DebuggerStepThrough]
        public static float? ExecFloatnInTran(this IDbConnection connection, string commandText)
        {
            return ExecScalarInTran(connection.OpenSafe(), commandText).ToFloatn();
        }
        [DebuggerStepThrough]
        public static float? ExecFloatnInTran(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecFloatnInTran(connection, ToQuery(connection, commandText, param));
        }

        #endregion


        #region ExecInt

        [DebuggerStepThrough]
        public static int ExecInt(this IDbConnection connection, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), commandText).ToInt();
        }
        [DebuggerStepThrough]
        public static int ExecInt(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText).ToInt();
        }
        [DebuggerStepThrough]
        public static int ExecInt(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), commandText, param).ToInt();
        }
        [DebuggerStepThrough]
        public static int ExecInt(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText, param).ToInt();
        }

        [DebuggerStepThrough]
        public static int ExecIntInTran(this IDbConnection connection, string commandText)
        {
            return ExecScalarInTran(connection.OpenSafe(), commandText).ToInt();
        }
        [DebuggerStepThrough]
        public static int ExecIntInTran(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecIntInTran(connection, ToQuery(connection, commandText, param));
        }


        [DebuggerStepThrough]
        public static int? ExecIntn(this IDbConnection connection, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), commandText).ToIntn();
        }
        [DebuggerStepThrough]
        public static int? ExecIntn(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText).ToIntn();
        }
        [DebuggerStepThrough]
        public static int? ExecIntn(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), commandText, param).ToIntn();
        }
        [DebuggerStepThrough]
        public static int? ExecIntn(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText, param).ToIntn();
        }

        [DebuggerStepThrough]
        public static int? ExecIntnInTran(this IDbConnection connection, string commandText)
        {
            return ExecScalarInTran(connection.OpenSafe(), commandText).ToIntn();
        }
        [DebuggerStepThrough]
        public static int? ExecIntnInTran(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecIntnInTran(connection, ToQuery(connection, commandText, param));
        }

        #endregion


        #region ExecInt64

        [DebuggerStepThrough]
        public static Int64 ExecInt64(this IDbConnection connection, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), commandText).ToInt64();
        }
        [DebuggerStepThrough]
        public static Int64 ExecInt64(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText).ToInt64();
        }
        [DebuggerStepThrough]
        public static Int64 ExecInt64(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), commandText, param).ToInt64();
        }
        [DebuggerStepThrough]
        public static Int64 ExecInt64(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText, param).ToInt64();
        }

        [DebuggerStepThrough]
        public static Int64 ExecInt64InTran(this IDbConnection connection, string commandText)
        {
            return ExecScalarInTran(connection.OpenSafe(), commandText).ToInt64();
        }
        [DebuggerStepThrough]
        public static Int64 ExecInt64InTran(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecInt64InTran(connection, ToQuery(connection, commandText, param));
        }


        [DebuggerStepThrough]
        public static Int64? ExecInt64n(this IDbConnection connection, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), commandText).ToInt64n();
        }
        [DebuggerStepThrough]
        public static Int64? ExecInt64n(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText).ToInt64n();
        }
        [DebuggerStepThrough]
        public static Int64? ExecInt64n(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), commandText, param).ToInt64n();
        }
        [DebuggerStepThrough]
        public static Int64? ExecInt64n(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            return ExecScalar(connection.OpenSafe(), tran, commandText, param).ToInt64n();
        }

        [DebuggerStepThrough]
        public static Int64? ExecInt64nInTran(this IDbConnection connection, string commandText)
        {
            return ExecScalarInTran(connection.OpenSafe(), commandText).ToInt64n();
        }
        [DebuggerStepThrough]
        public static Int64? ExecInt64nInTran(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecInt64nInTran(connection, ToQuery(connection, commandText, param));
        }

        #endregion



        #region ExecArray

        [DebuggerStepThrough]
        public static T[] ExecArray<T>(this IDbCommand cmd, int colIndex = 0)
        {
            cmd.Connection.OpenSafe();
            var list = new List<T>(128);
            using (var r = cmd.ExecuteReader())
            {
                while (r.Read())
                {
                    list.Add(r[colIndex].To<T>());
                }
            }
            return list.ToArray();
        }

        [DebuggerStepThrough]
        public static T[] ExecArray<T>(this IDbConnection connection, string commandText)
        {
            return NewCommand(connection, commandText).ExecArray<T>();
        }
        [DebuggerStepThrough]
        public static T[] ExecArray<T>(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            return NewCommand(connection, tran, commandText).ExecArray<T>();
        }
        [DebuggerStepThrough]
        public static T[] ExecArray<T>(this IDbConnection connection, string commandText, params object[] param)
        {
            return NewCommand(connection, commandText, param).ExecArray<T>();
        }
        [DebuggerStepThrough]
        public static T[] ExecArray<T>(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            return NewCommand(connection, tran, commandText, param).ExecArray<T>();
        }

        [DebuggerStepThrough]
        public static T[] ExecArrayInTran<T>(this IDbConnection connection, string commandText)
        {
            connection.OpenSafe();
            using (var tran = connection.BeginTransaction())
            {
                try
                {
                    var result = connection.ExecArray<T>(tran, commandText);
                    tran.Commit();
                    return result;
                }
                catch
                {
                    tran.Rollback();
                    throw;
                }
            }
        }
        [DebuggerStepThrough]
        public static T[] ExecArrayInTran<T>(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecArrayInTran<T>(connection, ToQuery(connection, commandText, param));
        }


        [DebuggerStepThrough]
        public static object[] ExecArray(this IDbCommand cmd, int colIndex = 0)
        {
            cmd.Connection.OpenSafe();
            var list = new List<object>(128);
            using (var r = cmd.ExecuteReader())
            {
                while (r.Read())
                {
                    list.Add(r[colIndex]);
                }
            }
            return list.ToArray();
        }

        [DebuggerStepThrough]
        public static object[] ExecArray(this IDbConnection connection, string commandText)
        {
            connection.OpenSafe();
            return NewCommand(connection, commandText).ExecArray();
        }
        [DebuggerStepThrough]
        public static object[] ExecArray(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            connection.OpenSafe();
            return NewCommand(connection, tran, commandText).ExecArray();
        }
        [DebuggerStepThrough]
        public static object[] ExecArray(this IDbConnection connection, string commandText, params object[] param)
        {
            connection.OpenSafe();
            return NewCommand(connection, commandText, param).ExecArray();
        }
        [DebuggerStepThrough]
        public static object[] ExecArray(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            connection.OpenSafe();
            return NewCommand(connection, tran, commandText, param).ExecArray();
        }

        [DebuggerStepThrough]
        public static object[] ExecArrayInTran(this IDbConnection connection, string commandText)
        {
            connection.OpenSafe();
            using (var tran = connection.BeginTransaction())
            {
                try
                {
                    var result = connection.ExecArray(tran, commandText);
                    tran.Commit();
                    return result;
                }
                catch
                {
                    tran.Rollback();
                    throw;
                }
            }
        }
        [DebuggerStepThrough]
        public static object[] ExecArrayInTran(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecArrayInTran(connection, ToQuery(connection, commandText, param));
        }
        #endregion


        #region ExecuteRowArray

        [DebuggerStepThrough]
        public static T[] ExecuteRowArray<T>(this IDbCommand cmd)
        {
            cmd.Connection.OpenSafe();
            using (var r = cmd.ExecuteReader())
            {
                if (r.Read())
                {
                    var list = new T[r.FieldCount];
                    for (int i = 0, len = r.FieldCount; i < len; i++)
                    {
                        list[i] = r[i].To<T>();
                    }
                    return list;
                }
            }
            return null;
        }

        [DebuggerStepThrough]
        public static T[] ExecuteRowArray<T>(this IDbConnection connection, string commandText)
        {
            return NewCommand(connection, commandText).ExecuteRowArray<T>();
        }
        [DebuggerStepThrough]
        public static T[] ExecuteRowArray<T>(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            return NewCommand(connection, tran, commandText).ExecuteRowArray<T>();
        }
        [DebuggerStepThrough]
        public static T[] ExecuteRowArray<T>(this IDbConnection connection, string commandText, params object[] param)
        {
            return NewCommand(connection, commandText, param).ExecuteRowArray<T>();
        }
        [DebuggerStepThrough]
        public static T[] ExecuteRowArray<T>(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            return NewCommand(connection, tran, commandText, param).ExecuteRowArray<T>();
        }

        [DebuggerStepThrough]
        public static T[] ExecuteRowArrayInTran<T>(this IDbConnection connection, string commandText)
        {
            connection.OpenSafe();
            using (var tran = connection.BeginTransaction())
            {
                try
                {
                    var result = connection.ExecuteRowArray<T>(tran, commandText);
                    tran.Commit();
                    return result;
                }
                catch
                {
                    tran.Rollback();
                    throw;
                }
            }
        }
        [DebuggerStepThrough]
        public static T[] ExecuteRowArrayInTran<T>(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecuteRowArrayInTran<T>(connection, ToQuery(connection, commandText, param));
        }


        [DebuggerStepThrough]
        public static object[] ExecuteRowArray(this IDbCommand cmd)
        {
            cmd.Connection.OpenSafe();
            using (var r = cmd.ExecuteReader())
            {
                if (r.Read())
                {
                    var list = new object[r.FieldCount];
                    for (int i = 0, len = r.FieldCount; i < len; i++)
                    {
                        list[i] = r[i];
                    }
                    return list;
                }
            }
            return null;
        }

        [DebuggerStepThrough]
        public static object[] ExecuteRowArray(this IDbConnection connection, string commandText)
        {
            connection.OpenSafe();
            return NewCommand(connection, commandText).ExecuteRowArray();
        }
        [DebuggerStepThrough]
        public static object[] ExecuteRowArray(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            connection.OpenSafe();
            return NewCommand(connection, tran, commandText).ExecuteRowArray();
        }
        [DebuggerStepThrough]
        public static object[] ExecuteRowArray(this IDbConnection connection, string commandText, params object[] param)
        {
            connection.OpenSafe();
            return NewCommand(connection, commandText, param).ExecuteRowArray();
        }
        [DebuggerStepThrough]
        public static object[] ExecuteRowArray(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            connection.OpenSafe();
            return NewCommand(connection, tran, commandText, param).ExecuteRowArray();
        }

        [DebuggerStepThrough]
        public static object[] ExecuteRowArrayInTran(this IDbConnection connection, string commandText)
        {
            connection.OpenSafe();
            using (var tran = connection.BeginTransaction())
            {
                try
                {
                    var result = connection.ExecuteRowArray(tran, commandText);
                    tran.Commit();
                    return result;
                }
                catch
                {
                    tran.Rollback();
                    throw;
                }
            }
        }
        [DebuggerStepThrough]
        public static object[] ExecuteRowArrayInTran(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecuteRowArrayInTran(connection, ToQuery(connection, commandText, param));
        }

        #endregion


        #region ExecDictionary

        [DebuggerStepThrough]
        public static Dictionary<string, object> ExecDictionary(this IDbCommand cmd, Dictionary<string, object> dic)
        {
            cmd.Connection.OpenSafe();
            using (var r = cmd.ExecuteReader())
            {
                if (!r.Read()) return dic;
                for (int i = 0, len = r.FieldCount; i < len; i++)
                {
                    dic[r.GetName(i)] = r[i];
                }
            }
            return dic;
        }
        [DebuggerStepThrough]
        public static Dictionary<string, object> ExecDictionary(this IDbCommand cmd)
        {
            var dic = new Dictionary<string, object>();
            return ExecDictionary(cmd, dic);
        }

        [DebuggerStepThrough]
        public static Dictionary<string, object> ExecDictionary(this IDbConnection connection, Dictionary<string, object> dic, string commandText)
        {
            using (var cmd = NewCommand(connection, commandText))
            {
                return cmd.ExecDictionary(dic);
            }
        }
        [DebuggerStepThrough]
        public static Dictionary<string, object> ExecDictionary(this IDbConnection connection, Dictionary<string, object> dic, IDbTransaction tran, string commandText)
        {
            using (var cmd = NewCommand(connection, tran, commandText))
            {
                return cmd.ExecDictionary(dic);
            }
        }
        [DebuggerStepThrough]
        public static Dictionary<string, object> ExecDictionary(this IDbConnection connection, Dictionary<string, object> dic, string commandText, params object[] param)
        {
            using (var cmd = NewCommand(connection, commandText, param))
            {
                return cmd.ExecDictionary(dic);
            }
        }
        [DebuggerStepThrough]
        public static Dictionary<string, object> ExecDictionary(this IDbConnection connection, Dictionary<string, object> dic, IDbTransaction tran, string commandText, params object[] param)
        {
            using (var cmd = NewCommand(connection, tran, commandText, param))
            {
                return cmd.ExecDictionary(dic);
            }
        }

        [DebuggerStepThrough]
        public static Dictionary<string, object> ExecDictionary(this IDbConnection connection, string commandText)
        {
            return ExecDictionary(connection, new Dictionary<string, object>(), commandText);
        }
        [DebuggerStepThrough]
        public static Dictionary<string, object> ExecDictionary(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            return ExecDictionary(connection, new Dictionary<string, object>(), tran, commandText);
        }
        [DebuggerStepThrough]
        public static Dictionary<string, object> ExecDictionary(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecDictionary(connection, new Dictionary<string, object>(), commandText, param);
        }
        [DebuggerStepThrough]
        public static Dictionary<string, object> ExecDictionary(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            return ExecDictionary(connection, new Dictionary<string, object>(), tran, commandText, param);
        }

        [DebuggerStepThrough]
        public static Dictionary<string, object> ExecDictionaryInTran(this IDbConnection connection, Dictionary<string, object> dic, string commandText)
        {
            using (var tran = connection.BeginTransaction())
            {
                try
                {
                    var result = ExecDictionary(connection, dic, tran, commandText);
                    tran.Commit();
                    return result;
                }
                catch
                {
                    tran.Rollback();
                    throw;
                }
            }
        }
        [DebuggerStepThrough]
        public static Dictionary<string, object> ExecDictionaryInTran(this IDbConnection connection, Dictionary<string, object> dic, string commandText, params object[] param)
        {
            return ExecDictionaryInTran(connection, dic, ToQuery(connection, commandText, param));
        }
        [DebuggerStepThrough]
        public static Dictionary<string, object> ExecDictionaryInTran(this IDbConnection connection, string commandText)
        {
            return ExecDictionaryInTran(connection, new Dictionary<string, object>(), commandText);
        }
        [DebuggerStepThrough]
        public static Dictionary<string, object> ExecDictionaryInTran(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecDictionaryInTran(connection, new Dictionary<string, object>(), ToQuery(connection, commandText, param));
        }



        #endregion


        #region ExecRef

        [DebuggerStepThrough]
        public static SortedDictionary<object, object> ExecRef(this IDbCommand cmd, SortedDictionary<object, object> dic)
        {
            cmd.Connection.OpenSafe();
            using (var r = cmd.ExecuteReader())
            {
                if (r.FieldCount < 2)
                    throw new Exception("The field count of DataReader must be more or equal 2.");
                while (r.Read())
                {
                    dic[r[0]] = r[1];
                }
            }
            return dic;
        }
        /// <summary>
        /// Возвращает dictionary типа dic[ID] = name, для быстрого поиска имени по ключу
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>        
        [DebuggerStepThrough]
        public static SortedDictionary<object, object> ExecRef(this IDbCommand cmd)
        {
            var dic = new SortedDictionary<object, object>();
            return ExecRef(cmd, dic);
        }

        [DebuggerStepThrough]
        public static SortedDictionary<object, object> ExecRef(this IDbConnection connection, SortedDictionary<object, object> dic, string commandText)
        {
            using (var cmd = NewCommand(connection, commandText))
            {
                return cmd.ExecRef(dic);
            }
        }
        [DebuggerStepThrough]
        public static SortedDictionary<object, object> ExecRef(this IDbConnection connection, SortedDictionary<object, object> dic, IDbTransaction tran, string commandText)
        {
            using (var cmd = NewCommand(connection, tran, commandText))
            {
                return cmd.ExecRef(dic);
            }
        }
        [DebuggerStepThrough]
        public static SortedDictionary<object, object> ExecRef(this IDbConnection connection, SortedDictionary<object, object> dic, string commandText, params object[] param)
        {
            using (var cmd = NewCommand(connection, commandText, param))
            {
                return cmd.ExecRef(dic);
            }
        }
        [DebuggerStepThrough]
        public static SortedDictionary<object, object> ExecRef(this IDbConnection connection, SortedDictionary<object, object> dic, IDbTransaction tran, string commandText, params object[] param)
        {
            using (var cmd = NewCommand(connection, tran, commandText, param))
            {
                return cmd.ExecRef(dic);
            }
        }

        [DebuggerStepThrough]
        public static SortedDictionary<object, object> ExecRef(this IDbConnection connection, string commandText)
        {
            return ExecRef(connection, new SortedDictionary<object, object>(), commandText);
        }
        [DebuggerStepThrough]
        public static SortedDictionary<object, object> ExecRef(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            return ExecRef(connection, new SortedDictionary<object, object>(), tran, commandText);
        }
        [DebuggerStepThrough]
        public static SortedDictionary<object, object> ExecRef(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecRef(connection, new SortedDictionary<object, object>(), commandText, param);
        }
        [DebuggerStepThrough]
        public static SortedDictionary<object, object> ExecRef(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            return ExecRef(connection, new SortedDictionary<object, object>(), tran, commandText, param);
        }

        [DebuggerStepThrough]
        public static SortedDictionary<object, object> ExecRefInTran(this IDbConnection connection, SortedDictionary<object, object> dic, string commandText)
        {
            using (var tran = connection.BeginTransaction())
            {
                try
                {
                    var result = ExecRef(connection, dic, tran, commandText);
                    tran.Commit();
                    return result;
                }
                catch
                {
                    tran.Rollback();
                    throw;
                }
            }
        }
        [DebuggerStepThrough]
        public static SortedDictionary<object, object> ExecRefInTran(this IDbConnection connection, SortedDictionary<object, object> dic, string commandText, params object[] param)
        {
            return ExecRefInTran(connection, dic, ToQuery(connection, commandText, param));
        }
        [DebuggerStepThrough]
        public static SortedDictionary<object, object> ExecRefInTran(this IDbConnection connection, string commandText)
        {
            return ExecRefInTran(connection, new SortedDictionary<object, object>(), commandText);
        }
        [DebuggerStepThrough]
        public static SortedDictionary<object, object> ExecRefInTran(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecRefInTran(connection, new SortedDictionary<object, object>(), ToQuery(connection, commandText, param));
        }


        [DebuggerStepThrough]
        public static SortedDictionary<TKey, TValue> ExecRef<TKey, TValue>(this IDbCommand cmd, SortedDictionary<TKey, TValue> dic)
        {
            cmd.Connection.OpenSafe();
            using (var r = cmd.ExecuteReader())
            {
                if (r.FieldCount < 2)
                    throw new Exception("The field count of DataReader must be more or equal 2.");
                while (r.Read())
                {
                    dic[r[0].To<TKey>()] = r[1].To<TValue>();
                }
            }
            return dic;
        }
        /// <summary>
        /// Возвращает dictionary типа dic[ID] = name, для быстрого поиска имени по ключу
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>        
        [DebuggerStepThrough]
        public static SortedDictionary<TKey, TValue> ExecRef<TKey, TValue>(this IDbCommand cmd)
        {
            var dic = new SortedDictionary<TKey, TValue>();
            return ExecRef(cmd, dic);
        }

        [DebuggerStepThrough]
        public static SortedDictionary<TKey, TValue> ExecRef<TKey, TValue>(this IDbConnection connection, SortedDictionary<TKey, TValue> dic, string commandText)
        {
            using (var cmd = NewCommand(connection, commandText))
            {
                return cmd.ExecRef(dic);
            }
        }
        [DebuggerStepThrough]
        public static SortedDictionary<TKey, TValue> ExecRef<TKey, TValue>(this IDbConnection connection, SortedDictionary<TKey, TValue> dic, IDbTransaction tran, string commandText)
        {
            using (var cmd = NewCommand(connection, tran, commandText))
            {
                return cmd.ExecRef(dic);
            }
        }
        [DebuggerStepThrough]
        public static SortedDictionary<TKey, TValue> ExecRef<TKey, TValue>(this IDbConnection connection, SortedDictionary<TKey, TValue> dic, string commandText, params object[] param)
        {
            using (var cmd = NewCommand(connection, commandText, param))
            {
                return cmd.ExecRef(dic);
            }
        }
        [DebuggerStepThrough]
        public static SortedDictionary<TKey, TValue> ExecRef<TKey, TValue>(this IDbConnection connection, SortedDictionary<TKey, TValue> dic, IDbTransaction tran, string commandText, params object[] param)
        {
            using (var cmd = NewCommand(connection, tran, commandText, param))
            {
                return cmd.ExecRef(dic);
            }
        }

        [DebuggerStepThrough]
        public static SortedDictionary<TKey, TValue> ExecRef<TKey, TValue>(this IDbConnection connection, string commandText)
        {
            return ExecRef(connection, new SortedDictionary<TKey, TValue>(), commandText);
        }
        [DebuggerStepThrough]
        public static SortedDictionary<TKey, TValue> ExecRef<TKey, TValue>(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            return ExecRef(connection, new SortedDictionary<TKey, TValue>(), tran, commandText);
        }
        [DebuggerStepThrough]
        public static SortedDictionary<TKey, TValue> ExecRef<TKey, TValue>(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecRef(connection, new SortedDictionary<TKey, TValue>(), commandText, param);
        }
        [DebuggerStepThrough]
        public static SortedDictionary<TKey, TValue> ExecRef<TKey, TValue>(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            return ExecRef(connection, new SortedDictionary<TKey, TValue>(), tran, commandText, param);
        }

        [DebuggerStepThrough]
        public static SortedDictionary<TKey, TValue> ExecRefInTran<TKey, TValue>(this IDbConnection connection, SortedDictionary<TKey, TValue> dic, string commandText)
        {
            using (var tran = connection.BeginTransaction())
            {
                try
                {
                    var result = ExecRef(connection, dic, tran, commandText);
                    tran.Commit();
                    return result;
                }
                catch
                {
                    tran.Rollback();
                    throw;
                }
            }
        }
        [DebuggerStepThrough]
        public static SortedDictionary<TKey, TValue> ExecRefInTran<TKey, TValue>(this IDbConnection connection, SortedDictionary<TKey, TValue> dic, string commandText, params object[] param)
        {
            return ExecRefInTran(connection, dic, ToQuery(connection, commandText, param));
        }
        [DebuggerStepThrough]
        public static SortedDictionary<TKey, TValue> ExecRefInTran<TKey, TValue>(this IDbConnection connection, string commandText)
        {
            return ExecRefInTran(connection, new SortedDictionary<TKey, TValue>(), commandText);
        }
        [DebuggerStepThrough]
        public static SortedDictionary<TKey, TValue> ExecRefInTran<TKey, TValue>(this IDbConnection connection, string commandText, params object[] param)
        {
            return ExecRefInTran(connection, new SortedDictionary<TKey, TValue>(), ToQuery(connection, commandText, param));
        }

        #endregion


        #region DataReader

        //[DebuggerStepThrough]
        public static IDataReader NewDataReader(this IDbConnection connection, string commandText)
        {
            connection.OpenSafe();
            return NewCommand(connection, commandText).ExecuteReader();
        }
        [DebuggerStepThrough]
        public static IDataReader NewDataReader(this IDbConnection connection, string commandText, params object[] param)
        {
            connection.OpenSafe();
            return NewCommand(connection, connection.ToQuery(commandText, param)).ExecuteReader();
        }
        [DebuggerStepThrough]
        public static IDataReader NewDataReader(this IDbConnection connection, IDbTransaction tran, string commandText)
        {
            connection.OpenSafe();
            return NewCommand(connection, tran, commandText).ExecuteReader();
        }
        [DebuggerStepThrough]
        public static IDataReader NewDataReader(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            connection.OpenSafe();
            return NewCommand(connection, tran, connection.ToQuery(commandText, param)).ExecuteReader();
        }

        [DebuggerStepThrough]
        public static IEnumerable GetValues(this IDataReader reader)
        {
            for (int i = 0, len = reader.FieldCount; i < len; ++i)
            {
                yield return reader[i];
            }
        }

        #endregion


        //#region New DataSet

        //[DebuggerStepThrough]
        //public static DataSet FillDataSet(this DataSet dataSet, IDbConnection connection, DbDataAdapter dataAdapter,
        //    string commandText)
        //{
        //    connection.OpenSafe();
        //    dataSet.Clear();
        //    dataAdapter.SelectCommand = NewCommand(connection, commandText);
        //    dataAdapter.Fill(dataSet);
        //    return dataSet;
        //}
        //[DebuggerStepThrough]
        //public static DataSet NewDataSet(this IDbConnection connection, DbDataAdapter dataAdapter, string commandText)
        //{
        //    return FillDataSet(new DataSet(), connection, dataAdapter, commandText);
        //}

        //#endregion


        #region Select

        public static IEnumerable<T> Select<T>(this IDataReader r)
            where T : class, new()
        {
            var type = typeof(T);
            var fieldCount = r.FieldCount;
            var props = new MemberInfo[fieldCount];

            try
            {
                for (var i = 0; i < fieldCount; ++i)
                {
                    props[i] = type.GetPropertyOrField(r.GetName(i));
                }
            }
            catch
            {
                props = type.GetPropertiesAndFields();
            }

            while (r.Read())
            {
                var t = new T();
                for (var i = 0; i < fieldCount; ++i)
                {
                    if (props[i] != null)
                        props[i].SetValue(t, r[i].To(props[i].ResultType()), null);
                }
                yield return t;
            }
        }

        public static IEnumerable<Dictionary<string, object>> Select(this IDataReader r, Type fieldDescriptor = null)
        {
            var props = fieldDescriptor != null ? fieldDescriptor.GetPropertiesAndFields().ToArray() : null;
            var fieldCount = props == null ? r.FieldCount : Math.Min(props.Length, r.FieldCount);

            while (r.Read())
            {
                var t = new Dictionary<string, object>();
                for (var i = 0; i < fieldCount; ++i)
                {
                    var name = props != null ? props[i].Name : r.GetName(i);
                    var value = r.GetValue(i);
                    if (NormalizeValue != null) value = NormalizeValue(value);
                    t[name] = value;
                }
                yield return t;
            }
        }


        public static List<T> Select<T>(this IDbConnection connection, string commandText)
            where T : class, new()
        {
            using (var r = connection.NewDataReader(commandText))
            {
                return r.Select<T>().ToList();
            }
        }

        public static List<Dictionary<string, object>> Select(this IDbConnection connection, string commandText)
        {
            using (var r = connection.NewDataReader(commandText))
            {
                return r.Select().ToList();
            }
        }

        public static List<Dictionary<string, object>> Select(this IDbConnection connection, Type fieldDescriptor, string commandText)
        {
            using (var r = connection.NewDataReader(commandText))
            {
                return r.Select(fieldDescriptor).ToList();
            }
        }

        public static List<T> Select<T>(this IDbConnection connection, string commandText, params object[] param)
            where T : class, new()
        {
            return connection.Select<T>(connection.ToQuery(commandText, param));
        }

        public static List<Dictionary<string, object>> Select(this IDbConnection connection, string commandText, params object[] param)
        {
            return connection.Select(connection.ToQuery(commandText, param));
        }


        public static List<T> Select<T>(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
            where T : class, new()
        {
            using (var r = connection.NewDataReader(tran, connection.ToQuery(commandText, param)))
            {
                return r.Select<T>().ToList();
            }
        }

        public static List<Dictionary<string, object>> Select(this IDbConnection connection, IDbTransaction tran, string commandText, params object[] param)
        {
            using (var r = connection.NewDataReader(tran, connection.ToQuery(commandText, param)))
            {
                return r.Select().ToList();
            }
        }

        #endregion

    }
}
